package com.ltmall.api.dao.impl;

import com.ltmall.api.bean.*;
import com.ltmall.api.dao.IDirectSupplyDAO;
import com.ltmall.api.util.PageUtil;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.orm.hibernate5.HibernateCallback;
import org.springframework.orm.hibernate5.support.HibernateDaoSupport;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.ltmall.api.util.HibernateUtil.getSession;

public class DirectSupplyDAO extends HibernateDaoSupport implements IDirectSupplyDAO {

    @SuppressWarnings({ "rawtypes" })
    public List<ThDirectSupply> getOpenedProvince() {
        List<ThDirectSupply> supplyList = new ArrayList<ThDirectSupply>();
        //final String SQL = "SELECT DISTINCT(province) FROM th_direct_supply ";
        final String SQL = "SELECT MAX(id) id,province FROM th_direct_supply GROUP BY province";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                return query.list();
            }
        });
        for (int i = 0; i < list.size(); i++) {
            Object[] obj = (Object[]) list.get(i);
            ThDirectSupply supply = new ThDirectSupply();
            supply.setId(Integer.parseInt(obj[0].toString()));
            supply.setProvince(obj[1].toString());
            supplyList.add(supply);
        }
        return supplyList;
    }

    @SuppressWarnings({ "rawtypes" })
    public List<ThDirectSupply> getOpenedCityByProvince(final String province) {
        List<ThDirectSupply> supplyList = new ArrayList<ThDirectSupply>();
        //final String SQL = "SELECT DISTINCT(city) FROM th_direct_supply WHERE province=? ";
        final String SQL = "SELECT MAX(id) id,city FROM th_direct_supply WHERE province=? GROUP BY city";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, province);
                return query.list();
            }
        });
        for (int i = 0; i < list.size(); i++) {
            Object[] obj = (Object[]) list.get(i);
            ThDirectSupply supply = new ThDirectSupply();
            supply.setId(Integer.parseInt(obj[0].toString()));
            supply.setCity(obj[1].toString());
            supplyList.add(supply);
        }
        return supplyList;
    }

    @SuppressWarnings({ "rawtypes" })
    public List<ThDirectSupply> getOpenedCountyByCity(final String city) {
        List<ThDirectSupply> supplyList = new ArrayList<ThDirectSupply>();
        //final String SQL = "SELECT DISTINCT(county) FROM th_direct_supply WHERE city=? ";
        final String SQL = "SELECT MAX(id) id,county FROM th_direct_supply WHERE city=? GROUP BY county";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, city);
                return query.list();
            }
        });
        for (int i = 0; i < list.size(); i++) {
            Object[] obj = (Object[]) list.get(i);
            ThDirectSupply supply = new ThDirectSupply();
            supply.setId(Integer.parseInt(obj[0].toString()));
            supply.setCounty(obj[1].toString());
            supplyList.add(supply);
        }
        return supplyList;
    }

    @SuppressWarnings({ "rawtypes" })
    public List<ThDirectSupply> getOpenedTownByCounty(final String county) {
        List<ThDirectSupply> supplyList = new ArrayList<ThDirectSupply>();
        //final String SQL = "SELECT DISTINCT(town) FROM th_direct_supply WHERE county=? ";
        final String SQL = "SELECT MAX(id) id,town FROM th_direct_supply WHERE county=? GROUP BY town";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, county);
                return query.list();
            }
        });
        for (int i = 0; i < list.size(); i++) {
            Object[] obj = (Object[]) list.get(i);
            ThDirectSupply supply = new ThDirectSupply();
            supply.setId(Integer.parseInt(obj[0].toString()));
            supply.setTown(obj[1].toString());
            supplyList.add(supply);
        }
        return supplyList;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThDirectSupply> getOpenedBlockByTown(final String town) {
        List list = this.getHibernateTemplate().find("from ThDirectSupply WHERE town=? ",
            new Object[] { town });
        if (list.size() > 0) {
            return (List<ThDirectSupply>) list;
        }
        return null;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThDirectSupply> directSupplyPage(final PageUtil page) {

        final String HQL = "from ThDirectSupply ts where 1=1 order by ts.id desc ";
        List<ThDirectSupply> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    //分页
                    query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                        .setMaxResults(page.getNumPerPage());
                    return query.list();
                }
            });
        return list;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThGroupPersonalSetting> groupPersonalSettingPage(final PageUtil page) {

        final String SQL = "from ThGroupPersonalSetting ts where 1=1 order by ts.id desc  ";
        List<ThGroupPersonalSetting> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(SQL);
                    // 分页
                    query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                        .setMaxResults(page.getNumPerPage());
                    return query.list();
                }
            });
        return list;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThGroupBroadcast> groupBroadcastPage(final PageUtil page) {
        final String HQL = "from ThGroupBroadcast ts where 1=1 order by ts.id desc ";
        List<ThGroupBroadcast> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    //分页
                    query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                        .setMaxResults(page.getNumPerPage());
                    return query.list();
                }
            });
        return list;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThGroupBanners> groupBannersPage(final PageUtil page) {
        final String HQL = "from ThGroupBanners ts where 1=1 order by ts.id desc ";
        List<ThGroupBanners> list = this.getHibernateTemplate().execute(
            new HibernateCallback<List>() {
                public List doInHibernate(Session session) throws HibernateException, SQLException {
                    Query query = session.createQuery(HQL);
                    //分页
                    query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                        .setMaxResults(page.getNumPerPage());
                    return query.list();
                }
            });
        return list;
    }

    @Override
    public boolean openDirectSupply(ThDirectSupply info) {
        info.setGmtCreate(new Date());
        info.setGmtModified(new Date());
        try {
            getHibernateTemplate().persist(info);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThDirectSupply> getDirectSupplyListByName(String area, String areaName) {
        String sql = "from ThDirectSupply ds where ds." + area + " =?";
        List list = this.getHibernateTemplate().find(sql, new Object[] { areaName });
        if (list.size() > 0) {
            return (List<ThDirectSupply>) list;
        }
        return null;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThDirectSupply> getDirectSupplyList() {
        List list = this.getHibernateTemplate().find("from ThDirectSupply ", new Object[] {});
        if (list.size() > 0) {
            return (List<ThDirectSupply>) list;
        }
        return null;
    }

    @SuppressWarnings({ "rawtypes" })
    public ThDirectSupply getDirectSupplyById(String id) {
        List list = this.getHibernateTemplate().find("from ThDirectSupply ds where ds.id =?",
            new Object[] { new Integer(id) });
        if (list.size() > 0) {
            return (ThDirectSupply) list.get(0);
        }
        return null;
    }

    @SuppressWarnings({ "rawtypes" })
    public List<ThDirectSupply> getDirectSupplyByShopId(String shopId) {
        List list = this.getHibernateTemplate().find("from ThDirectSupply ds where ds.shopId =?",
            new Object[] { new Integer(shopId) });
        if (list.size() > 0) {
            return (List<ThDirectSupply>) list;
        }
        return null;
    }

    @Override
    public boolean updateDirectSupply(ThDirectSupply directSupply) {
        try {
            getHibernateTemplate().update(directSupply);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    public boolean updateDirectSupplyState(int id, int state) {
        String hql = "update ThDirectSupply da set da.state = ? where da.id = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, state);
            query.setParameter(1, id);
            int number = query.executeUpdate();
            if (number == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ThDirectSupply> getNearbyBlock(final double maxLat, final double minLat,
                                               final double maxLng, final double minLng) {
        List list = this
            .getHibernateTemplate()
            .find(
                "from ThDirectSupply ds where ds.latitude>=? and ds.latitude<=? and ds.longitude>=? and ds.longitude<=? and ds.state=1",
                new Object[] { minLat, maxLat, minLng, maxLng });
        if (list != null && list.size() > 0) {
            return (List<ThDirectSupply>) list;
        }
        return null;
    }

    //小区banner服务
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThGroupBanners> getGroupBannersList(int directSupplyId, int state) {
        List list = this.getHibernateTemplate().find(
            "from ThGroupBanners WHERE directSupplyId=? and state=? ",
            new Object[] { directSupplyId, state });
        if (list.size() > 0) {
            return (List<ThGroupBanners>) list;
        }
        return null;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThGroupBanners> getGroupBannersListByDirctId(int shopId, int directId) {
        List list = this.getHibernateTemplate().find(
            "from ThGroupBanners WHERE shopId=? and directSupplyId=? ",
            new Object[] { shopId, directId });
        if (list.size() > 0) {
            return (List<ThGroupBanners>) list;
        }
        return null;
    }

    public boolean updateGroupBannersState(int id, int state) {
        String hql = "update ThGroupBanners da set da.state = ? where da.id = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, state);
            query.setParameter(1, id);
            int number = query.executeUpdate();
            if (number == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    //小区团购公告服务
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThGroupBroadcast> getGroupBroadcastList(int directSupplyId, int state) {
        List list = this.getHibernateTemplate().find(
            "from ThGroupBroadcast WHERE directSupplyId=? and state=? ",
            new Object[] { directSupplyId, state });
        if (list.size() > 0) {
            return (List<ThGroupBroadcast>) list;
        }
        return null;
    }

    public boolean updateGroupBroadcastState(int id, int state) {
        String hql = "update ThGroupBroadcast da set da.state = ? where da.id = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, state);
            query.setParameter(1, id);
            int number = query.executeUpdate();
            if (number == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    @SuppressWarnings("rawtypes")
    public ThGroupBanners getGroupBanners(int id) {
        List list = this.getHibernateTemplate().find("from ThGroupBanners ds where ds.id =?",
            new Object[] { new Integer(id) });
        if (list.size() > 0) {
            return (ThGroupBanners) list.get(0);
        }
        return null;
    }

    @Override
    public boolean addGroupBanners(ThGroupBanners banners) {
        try {
            getHibernateTemplate().persist(banners);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @Override
    public boolean deleteGroupBanners(int id) {
        String hql = "delete ThGroupBanners gb where gb.id = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, id);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateGroupBanners(ThGroupBanners banners) {
        try {
            getHibernateTemplate().update(banners);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    @Override
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<ThGroupBroadcast> getGroupBroadcastList(int shopId, String directId) {
        List list = this.getHibernateTemplate().find(
            "from ThGroupBroadcast WHERE shopId=? and directSupplyId=? ",
            new Object[] { shopId, new Integer(directId) });
        if (list.size() > 0) {
            return (List<ThGroupBroadcast>) list;
        }
        return null;
    }

    @Override
    @SuppressWarnings("rawtypes")
    public ThGroupBroadcast getGroupBroadcast(int id) {
        List list = this.getHibernateTemplate().find("from ThGroupBroadcast ds where ds.id =?",
            new Object[] { new Integer(id) });
        if (list.size() > 0) {
            return (ThGroupBroadcast) list.get(0);
        }
        return null;
    }

    @Override
    public boolean addGroupBroadcast(ThGroupBroadcast groupBroadcast) {
        try {
            getHibernateTemplate().persist(groupBroadcast);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @Override
    public boolean updateGroupBroadcast(ThGroupBroadcast groupBroadcast) {
        try {
            getHibernateTemplate().update(groupBroadcast);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean deleteGroupBroadcast(int id) {
        String hql = "delete ThGroupBroadcast gb where gb.id = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, id);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public ThGroupPersonalSetting getGroupPersonalSetting(int shopId, String directId) {
        @SuppressWarnings("rawtypes")
        List list = this.getHibernateTemplate().find(
            "from ThGroupPersonalSetting WHERE shopId=? and directSupplyId=? ",
            new Object[] { shopId, new Integer(directId) });
        if (list.size() > 0) {
            return (ThGroupPersonalSetting) list.get(0);
        }
        return null;
    }

    @Override
    public ThGroupPersonalSetting getGroupPersonalSettingByState(int directSupplyId, int state) {
        List list = this.getHibernateTemplate().find(
            "from ThGroupPersonalSetting ds where ds.directSupplyId =? and  ds.state =?  ",
            new Object[] { directSupplyId, state });
        if (list.size() > 0) {
            return (ThGroupPersonalSetting) list.get(0);
        }
        return null;
    }

    public boolean updateGroupPersonalSettingState(int id, int state) {
        String hql = "update ThGroupPersonalSetting da set da.state = ? where da.id = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, state);
            query.setParameter(1, id);
            int number = query.executeUpdate();
            if (number == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean addGroupPersonalSetting(ThGroupPersonalSetting groupPersonalSetting) {
        try {
            getHibernateTemplate().persist(groupPersonalSetting);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @Override
    public boolean updateGroupPersonalSetting(ThGroupPersonalSetting groupPersonalSetting) {
        try {
            getHibernateTemplate().update(groupPersonalSetting);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean saveGroupDeliveryInfo(ThGroupDeliveryAddr deliveryAddr) {
        try {
            getHibernateTemplate().persist(deliveryAddr);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @Override
    public boolean updateGroupDeliveryInfo(int id, String name, String phone, String address) {
        String hql = "update ThGroupDeliveryAddr da set da.name = ?,da.phone = ?,da.address = ? "
                     + "where da.id = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, name);
            query.setParameter(1, phone);
            query.setParameter(2, address);
            query.setParameter(3, id);
            int number = query.executeUpdate();
            if (number == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public ThGroupDeliveryAddr getGroupDeliveryInfo(String userId, String directId) {
        List list = this.getHibernateTemplate().find(
            "from ThGroupDeliveryAddr WHERE directSupplyId=? and userId=? ",
            new Object[] { new Integer(directId), new Integer(userId) });
        if (list.size() > 0) {
            return (ThGroupDeliveryAddr) list.get(0);
        }
        return null;
    }

    @Override
    public ThGroupDeliveryAddr getGroupDeliveryInfoById(int id) {
        List list = this.getHibernateTemplate().find("from ThGroupDeliveryAddr WHERE id=?  ",
            new Object[] { id });
        if (list.size() > 0) {
            return (ThGroupDeliveryAddr) list.get(0);
        }
        return null;
    }
}
